import datetime
from PyQt5.QtCore import Qt, QThread, pyqtSignal, pyqtSlot
from PyQt5.QtWidgets import QWidget, QTableWidgetItem
from qfluentwidgets import InfoBarPosition, InfoBar
from ui.ticket_show_all_ui import Ui_ticket_show_all
from dateutil.relativedelta import relativedelta
from src.db_utils import *

class ticket_show_cls(Ui_ticket_show_all, QWidget):

    def __init__(self, parent=None):
        super().__init__(parent=parent)
        self.setupUi(self)
        self.show_month.clicked.connect(lambda: self.show_ticket_info("show_month"))
        self.show_havent_done.clicked.connect(lambda: self.show_ticket_info("show_havent_done"))
        self.show_ticket_time.clicked.connect(lambda: self.show_ticket_info("show_ticket_time"))
        self.show_time_todo.clicked.connect(lambda: self.show_ticket_info("show_time_todo")) # 新增的按钮
        self.submit_button.clicked.connect(self.update_ticket_info)
        self.updated_data = {}
        self.current_button_id = None # 初始化当前按钮ID变量

    def show_ticket_info(self, button_id):
        # 将当前点击的按钮ID保存起来，供update_ticket_info使用
        self.current_button_id = button_id
        
        db = Database()
        c = db.get_connection()
        
        self.TableWidget.clearContents()
        self.TableWidget.setRowCount(0) # 清空旧数据行
        self.TableWidget.verticalHeader().hide()
        self.TableWidget.resizeRowsToContents()

        # 根据按钮类型设置不同的表头和查询
        if button_id == "show_ticket_time" or button_id == "show_time_todo":
            # show_ticket_time 和 show_time_todo 共用同一套表头
            self.TableWidget.setHorizontalHeaderLabels([
                '开始时间', '结束时间', '工单编号', '用时', '工单ID', 
                '是否删除', '工单状态', '是否工时', '工作内容', 'id', '', '', '', '', ''
            ])
            col_count = 10 # 这两个查询都返回10个字段
            
            start_date_str = (datetime.datetime.today() - relativedelta(months=3))
            start_time = start_date_str.replace(day=1, hour=0, minute=0, second=0) # 优化：从月初开始
            
            if button_id == "show_ticket_time":
                ticket_query = ("SELECT start_time, end_time, ticket_code, use_time, ticket_id, is_del, ticket_time_status, is_worktime, work_contect, id "
                                " FROM ticket_time WHERE start_time > ?"
                                " ORDER BY start_time DESC")
                c.execute(ticket_query, (start_time,))
            else: # button_id == "show_time_todo"
                # 新增的查询逻辑：is_del为1 且 ticket_time_status不为1
                ticket_query = ("SELECT start_time, end_time, ticket_code, use_time, ticket_id, is_del, ticket_time_status, is_worktime, work_contect, id "
                                " FROM ticket_time WHERE is_del = '1' OR ((is_del ='' OR is_del IS NULL) AND (ticket_time_status IS NULL OR ticket_time_status ='' ))" 
                                " ORDER BY start_time DESC")
                c.execute(ticket_query)
        else:
            # show_month 和 show_havent_done 共用旧的表头
            self.TableWidget.setHorizontalHeaderLabels([
                '开始时间','结束时间', '工单编号', '用时','姓名', '工单标题',
                '是否重要','工单状态','是否删除', '是否本地','是否工时','工单状态','问题', '结论', 'id'
            ])
            col_count = 15 # 这两个查询返回15个字段
            
            if button_id == "show_month":
                start_date_str = (datetime.datetime.today() - relativedelta(months=1))
                start_time = start_date_str.replace(day=1, hour=0, minute=0, second=0) # 优化：从月初开始
                ticket_query = ("SELECT start_time, end_time, ticket_code, use_time, custom_name, ticket_title, "
                        "is_import, ticket_status, is_del, is_local, is_worktime, column_ticket_status, question_ask, question_done, id "
                        " FROM ticket WHERE start_time > ? AND (is_del <> '1' OR is_del is null)"
                        " ORDER BY start_time DESC")
                c.execute(ticket_query, (start_time,))
            elif button_id == "show_havent_done":
                ticket_query = ("SELECT start_time, end_time, ticket_code, use_time, custom_name, ticket_title, "
                        "is_import, ticket_status, is_del, is_local, is_worktime, column_ticket_status, question_ask, question_done, id "
                        " FROM ticket WHERE (column_ticket_status LIKE '%已开启%' or column_ticket_status LIKE '%待回应%') AND (is_del <> '1' OR is_del is null)"
                        " ORDER BY start_time DESC")
                c.execute(ticket_query)

        ticket_sheet = c.fetchall()
        # 将查询结果填充到表格中
        for i, integrator in enumerate(ticket_sheet):
            self.TableWidget.insertRow(i) # 插入新行
            for j in range(col_count):
                if j < len(integrator): # 防止索引越界
                    value = integrator[j]
                    str_tab = str(value) if value is not None else ''
                    self.TableWidget.setItem(i, j, QTableWidgetItem(str_tab))
        self.TableWidget.resizeColumnsToContents()
        db.close_connection() # 记得关闭数据库连接

    def update_ticket_info(self):
        # 检查是否有有效的当前按钮ID
        if not self.current_button_id:
            InfoBar.warning(
                title='操作无效',
                content='请先点击查询按钮加载数据',
                parent=self
            )
            return

        db = Database()
        c = db.get_connection()
        
        # 根据当前活跃的按钮类型，确定ID所在列和需要读取的列数
        if self.current_button_id == "show_ticket_time" or self.current_button_id == "show_time_todo":
            # 这两个按钮都对应 ticket_time 表
            id_col = 9 # ID在第10列（索引为9）
            data_cols = 9 # 需要更新的字段是前9列
        else:
            # 对应 ticket 表
            id_col = 14 # ID在第15列（索引为14）
            data_cols = 14 # 需要更新的字段是前14列
        
        # 收集更新数据
        updated_data = {}
        for row in range(self.TableWidget.rowCount()):
            item_id = self.TableWidget.item(row, id_col)
            if item_id and item_id.text():
                record_id = item_id.text()
                row_data = []
                for col in range(data_cols):
                    item = self.TableWidget.item(row, col)
                    row_data.append(item.text() if item else '')
                updated_data[record_id] = row_data
        
        # 根据当前表类型执行更新
        if not updated_data:
            InfoBar.info(
                title='提示',
                content='没有检测到需要更新的数据',
                parent=self
            )
            db.close_connection()
            return

        try:
            if self.current_button_id == "show_ticket_time" or self.current_button_id == "show_time_todo":
                for record_id, row_data in updated_data.items():
                    self.update_ticket_time_in_db(c, record_id, row_data)
            else:
                for record_id, row_data in updated_data.items():
                    self.update_ticket_in_db(c, record_id, row_data)
            
            db.commit_database() # 提交事务
            InfoBar.success(
                title='提交成功',
                content=f'共 {len(updated_data)} 条数据已更新',
                parent=self
            )
            # 刷新表格以显示最新数据
            self.show_ticket_info(self.current_button_id)
        except Exception as e:
            InfoBar.error(
                title='提交失败',
                content=f'更新数据库时发生错误: {e}',
                parent=self
            )
        finally:
            db.close_connection() # 确保连接被关闭

    # 单独处理ticket表的更新
    def update_ticket_in_db(self, c, record_id, row_data):
        # 注意：这里的列名和顺序必须和 row_data 中的数据完全对应
        update_query = ("UPDATE ticket SET start_time = ?, end_time = ?, ticket_code = ?, use_time = ?, custom_name = ?, ticket_title = ?, "
                        "is_import = ?, ticket_status = ?, is_del = ?, is_local = ?, is_worktime = ?, column_ticket_status = ?, question_ask = ?, question_done = ? WHERE id = ?")
        c.execute(update_query, (*row_data, record_id))

    # 处理ticket_time表的更新
    def update_ticket_time_in_db(self, c, record_id, row_data):
        # 注意：字段顺序必须和 SELECT 以及 UI 表格中的顺序一致
        # 对应 SELECT: start_time, end_time, ticket_code, use_time, ticket_id, is_del, ticket_time_status, is_worktime, work_contect
        update_query = ("UPDATE ticket_time SET start_time = ?, end_time = ?, ticket_code = ?, use_time = ?, ticket_id = ?, is_del = ?, ticket_time_status = ?, is_worktime = ?, work_contect = ? WHERE id = ?")
        c.execute(update_query, (*row_data, record_id))